﻿CREATE proc spReportApplicationPercentage
as
Select 
	ProgramId as [Program ID]
	, ProgramName  as [Program Name]
	, bow.ProjectId as [Project ID]
	, ProjectName as [Project Name]
	, [Application].Id as [Application ID]
	, [Application].Name as [Application Name]
	, ISNULL(ApplicationPercentage.Percentage , 100) as [Application %]
	, SystemGoc.ManagedGeography as [System Geography]
	, ClientGoc.ManagedGeography as [Client Geography]
	, ProgramInvestimentCategory as [Program Investment Category]
FROM ApplicationBookOfWork2014 bow
INNER JOIN [Application] ON [Application].Id = bow.ApplicationId
INNER JOIN Goc SystemGoc ON SystemGOCCode = SystemGoc.GocAttributes AND SystemGoc.ManagedGeography <> 'BR'
INNER JOIN Goc ClientGoc ON ClientGOCCode = ClientGoc.GocAttributes AND ClientGoc.ManagedGeography = 'BR'
LEFT JOIN ApplicationPercentage ON ApplicationPercentage.ProjectId = bow.ProjectId AND ApplicationPercentage.ApplicationId = Application.Id
ORDER BY ProgramId, bow.ProjectId, Application.Id